package tritop.android.a4dinfo;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.content.Context;
import android.content.res.AssetManager;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHelper extends SQLiteOpenHelper {
	
	public static final String DATABASE_NAME="deustats.db";
	public static final int DATABASE_VERSION=1;
	private static final String LOGTAG="DBHELPER";
	
	private static final String STATS_DESC_TABLENAME="STATS_DESC";
	private static final String CREATE_STATS_DESC_TABLE_STATEMENT="CREATE TABLE IF NOT EXISTS "+
	STATS_DESC_TABLENAME+"(_id integer PRIMARY KEY AUTOINCREMENT," +
	"DESCRIPTION"+" varchar(500),"+
	"VALUE1TYPE"+" varchar(20),"+
	"VALUE2TYPE"+" varchar(20),"+
	"VALUE1DESC"+" varchar(20),"+
	"VALUE2DESC"+" varchar(20)"+
	")";
	
	private static final String STATS_ENTRIES_TABLENAME="STATS_ENTRIES";
	private static final String CREATE_STATS_ENTRY_TABLE_STATEMENT="CREATE TABLE IF NOT EXISTS "+
	STATS_ENTRIES_TABLENAME+"(_id integer PRIMARY KEY AUTOINCREMENT," +
	"STATSID"+" integer," +
	"COUNTRYID"+" integer,"+
	"VALUE1"+" varchar(50),"+
	"VALUE2"+" varchar(50)"+
	")";
	
	private Context mContext;
	private SQLiteDatabase mDB;
	
	public DBHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		this.mContext = context;
		dbSetup();
		mDB=getWritableDatabase();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_STATS_DESC_TABLE_STATEMENT);
		db.execSQL(CREATE_STATS_ENTRY_TABLE_STATEMENT);
	}

	private void dbSetup() {
		if(!databaseExists()){
			this.getReadableDatabase();
			Log.e(LOGTAG,"copying DB from asset folder");
			copyAsssetDatabase();
		}
		else {
			Log.e(LOGTAG,"DB found");
		}
	}

	
	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// TODO Auto-generated method stub

	}

	public Map<Integer,List<String[]>> getAllEntries(int statsid){
		Map<Integer,List<String[]>> entries = new HashMap<Integer,List<String[]>>();
		List<Integer> countryIds= new ArrayList<Integer>();
		if(!mDB.isOpen()){
			mDB = getWritableDatabase();
		}
		
		//First get all countryids that are used in this stats
		Cursor cur=mDB.rawQuery("SELECT DISTINCT COUNTRYID FROM "+STATS_ENTRIES_TABLENAME+" where STATSID="+String.valueOf(statsid), null);
		if(cur != null){
			cur.moveToFirst();
			while (cur.isAfterLast() == false) {
				//Log.e(LOGTAG,"found id "+cur.getString(0));
				countryIds.add(Integer.parseInt(cur.getString(0)));
	       	    cur.moveToNext();
	        }
	        cur.close();
		}
		
		//now get the stats entries for all countryids we found
		for(int contryid:countryIds){
			entries.put(contryid, getEntries(statsid,contryid));
		}
		
		return entries;
	}
	
	public List<String[]> getEntries(int statsid,int countryid){
		List<String[]> entries = new ArrayList<String[]>();
		String[] pair;
		if(!mDB.isOpen()){
			mDB = getWritableDatabase();
		}
		Cursor cur=mDB.query(STATS_ENTRIES_TABLENAME, new String[]{"VALUE1","VALUE2"}, " STATSID=? AND COUNTRYID=?", new String[]{String.valueOf(statsid),String.valueOf(countryid)}, null, null,null, null);
		if(cur != null){
			cur.moveToFirst();
			while (cur.isAfterLast() == false) {
				//Log.e(LOGTAG,"found values "+cur.getString(0)+" "+cur.getString(1));
				pair = new String[2];
				pair[0]=cur.getString(0);
				pair[1]=cur.getString(1);
				entries.add(pair);
	       	    cur.moveToNext();
	        }
	        cur.close();
		}
		Collections.sort(entries,getComparator("YEAR"));
		return entries;
	}
	
	
	private Comparator<String[]> getComparator(String type){
		Comparator<String[]> comp=null;
		
		//VALUETYPE = YEAR (1999,2000 etc..)
		if(type.equals("YEAR")){
			comp = new Comparator<String[]>(){
				@Override
				public int compare(String[] object1, String[] object2) {
					int first=Integer.parseInt(object1[1]);
					int second=Integer.parseInt(object2[1]);
					if(first < second){
						return -1;
					}
					else if(first > second){
						return 1;
					}
					else {
						return 0;
					}
				}
			};
		}
		return comp;
	}
	
	public int getIdbyDescription(String description){
		int id=-1;
		if(!mDB.isOpen()){
			mDB = getWritableDatabase();
		}
		Cursor cur=mDB.query(STATS_DESC_TABLENAME, new String[]{"_id"}, " DESCRIPTION=? ", new String[]{description}, null, null,null, null);
		if(cur != null){
			cur.moveToFirst();
			while (cur.isAfterLast() == false) {
				//Log.e(LOGTAG,"getIDbyDesc "+cur.getString(0));
				id=(int) cur.getLong(0);
	       	    cur.moveToNext();
	        }
	        cur.close();
	        return id;
		}
		return 0;
	}
	
	public String[] getValueDescriptions(int id){
		String[] desc = new String[2];
		if(!mDB.isOpen()){
			mDB = getWritableDatabase();
		}
		Cursor cur=mDB.query(STATS_DESC_TABLENAME, new String[]{"VALUE1DESC","VALUE2DESC"}, " _id=? ", new String[]{String.valueOf(id)}, null, null,null, null);
		if(cur != null){
			cur.moveToFirst();
			while (cur.isAfterLast() == false) {
				desc[0]=cur.getString(0);
				desc[1]=cur.getString(1);
	       	    cur.moveToNext();
	        }
	        cur.close();
	        return desc;
		}
		return desc;
	}
	
	
	public String[] getAvailableStats(int countryid){
		StringBuilder sb = new StringBuilder();
		List<String> statsDesc = new ArrayList<String>();
		if(!mDB.isOpen()){
			mDB = getWritableDatabase();
		}
		Cursor cur=mDB.query(STATS_ENTRIES_TABLENAME, new String[]{"DISTINCT(STATSID)"}, " COUNTRYID=?", new String[]{String.valueOf(countryid)}, null, null,null, null);
		if(cur != null){
			cur.moveToFirst();
			while (cur.isAfterLast() == false) {
				//Log.e(LOGTAG,"found statsids values "+cur.getString(0));
				sb.append(cur.getString(0));
				sb.append(",");
	       	    cur.moveToNext();
	        }
			if(sb.charAt(sb.length()-1)==','){
				sb.deleteCharAt(sb.length()-1);
			}
	        cur.close();
		}
		cur=mDB.rawQuery("SELECT DESCRIPTION from "+STATS_DESC_TABLENAME+" WHERE _id IN ("+sb.toString()+")",null);
		if(cur != null){
			cur.moveToFirst();
			while (cur.isAfterLast() == false) {
				statsDesc.add(cur.getString(0));
	       	    cur.moveToNext();
	        }
	        cur.close();
		}
		return (String[])statsDesc.toArray(new String[statsDesc.size()]);
	}
	
	private boolean databaseExists(){
		
		File appDir = mContext.getFilesDir();
		String dbDirPath = appDir.getAbsolutePath();
		SQLiteDatabase tmpdb;
		
		if(dbDirPath.endsWith("files")){
			dbDirPath=dbDirPath.substring(0,dbDirPath.length()-5);
		}
		dbDirPath+="databases/";
		
		try {
			tmpdb = SQLiteDatabase.openDatabase(dbDirPath+DATABASE_NAME, null, SQLiteDatabase.OPEN_READONLY);
		}
		catch(SQLiteException e){
			return false;
		}
		
		if(tmpdb!=null){
			tmpdb.close();
			return true;
		}
		else return false;
	}
	
	
	private void copyAsssetDatabase(){
		byte buffer[] = new byte[1024];
		int count;
		InputStream inp=null;
		OutputStream outp=null;
		AssetManager assmgr = mContext.getAssets();
		
		try {
			
			File appDir = mContext.getFilesDir();
			String dbDirPath = appDir.getAbsolutePath();
			if(dbDirPath.endsWith("files")){
				dbDirPath=dbDirPath.substring(0,dbDirPath.length()-5);
			}
			dbDirPath+="databases/";
			
			inp=assmgr.open(DATABASE_NAME);
			outp = new FileOutputStream(dbDirPath+DATABASE_NAME);
	
			while((count=inp.read(buffer)) != -1){
				outp.write(buffer, 0, count);
			}
			inp.close();
			outp.close();
			Log.e(LOGTAG,"copyAssetDatabase finished");
		} catch (IOException e) {
			
			Log.e(LOGTAG,"copyAssetDatabase failed");
			e.printStackTrace();
			
		}
	}
	
	@Override
	public synchronized void close() {
		if(mDB!=null){
			mDB.close();
		}
		super.close();
	}
	
}
